﻿set ansi_padding, ansi_warnings, concat_null_yields_null, arithabort, quoted_identifier, ansi_nulls on
go

if exists (select * from tempdb..sysobjects where id=object_id('tempdb..#tmpProperties')) drop table #tmpProperties
go

create table #tmpProperties ( name nvarchar(255), value nvarchar(255) )
go

insert into #tmpProperties values('script', 'update.0011.sql')
insert into #tmpProperties values('error', '0')

set xact_abort on
go

set transaction isolation level serializable
go

begin transaction
go

set ansi_nulls on
set quoted_identifier on
go
/* BEGIN UPDATE SCRIPT */


if object_id('[dbo].[GetCallHistoryForInboundCall]') is not null drop proc [dbo].[GetCallHistoryForInboundCall]
go
create proc [dbo].[GetCallHistoryForInboundCall] @phoneNumber varchar(50), @callId uniqueidentifier
as
	select	c.*,
			dbo.GetTopicNamesByCallId(c.id, ', ', '') as TOPICS
	from	dbo.vwCalls c
	where	c.id <> @callId
			and c.from_address = @phoneNumber
	order by c.date_end desc
go

if object_id('dbo.AssignInboundCallToCase') is not null drop proc dbo.AssignInboundCallToCase
go
create proc dbo.AssignInboundCallToCase
	@callId uniqueidentifier,
	@caseId uniqueidentifier,
	@currentUserId uniqueidentifier
as
	set rowcount 1
	update	dbo.calls
	set		parent_id = @caseId,
			parent_type = 'Case',
			modified_user_id = @currentUserId,
			date_modified = getdate(),
			date_modified_utc = getdate()
	where	id = @callId

	set rowcount 0	
go

if object_id('dbo.ConvertInboundCallToCase') is not null drop proc dbo.ConvertInboundCallToCase
go
create proc dbo.ConvertInboundCallToCase
	@caseId uniqueidentifier,
	@callId uniqueidentifier,
	@assignedUserId uniqueidentifier,
	@description nvarchar(max),
	@resolution nvarchar(max),
	@subject nvarchar(255),
	@status nvarchar(25),
	@priority nvarchar(25),
	@currentUserId uniqueidentifier
as
	declare @caseNumber bigint
	declare @userName nvarchar(255)
	declare @customerName nvarchar(255)
	declare @customerId uniqueidentifier

	select	top 1
			@customerId = parent_id
	from	dbo.calls
	where	id = @callId

	select	@caseNumber = coalesce((select max(cast(case_number as bigint)) from dbo.cases), 0) + 1,
			@userName = (select top 1 name from dbo.accounts where id = @currentUserId),
			@customerName = (select top 1 name from dbo.accounts where id = @customerId)

	-- create case
	insert into dbo.cases (
			id,
			created_by,
			date_entered,
			assigned_user_id,
			case_number,
			name,
			account_name,
			account_id,
			status,
			priority,
			description,
			resolution
		)
		values (
			@caseId,
			@currentUserId,
			getdate(),
			@assignedUserId,
			@caseNumber,
			@subject,
			@customerName,
			@customerId,
			@status,
			@priority,
			@description,
			@resolution
		)
	
	-- assign call to case
	exec dbo.AssignInboundCallToCase @callId, @caseId, @currentUserId
go

if object_id('[dbo].[GetTopicNamesByCallId]') is not null drop function [dbo].[GetTopicNamesByCallId]
go
create function [dbo].[GetTopicNamesByCallId] (
	@callId uniqueidentifier,
	@separator nvarchar(50),
	@default nvarchar(255)
	)
	returns nvarchar(max)
as
	begin
	declare @result nvarchar(max)
	set @result = ''
	select	@result = case
				when @result = '' then name
				else @result + @separator + name
			end
	from
			dbo.GetTopicsByCallIdAsTable(@callId)
	order by
			name
	if len(@result) > 0
		set @result = substring(@result, len(@separator), len(@result))
	else
		set @result = @default
	return @result
	end
go


if object_id('[dbo].[GetTopicsByCallIdAsTable]') is not null drop function [dbo].[GetTopicsByCallIdAsTable]
go
create function [dbo].[GetTopicsByCallIdAsTable] (
		@callId uniqueidentifier
	)
	returns @result table (
		id uniqueidentifier,
		name nvarchar(255),
		parent_id uniqueidentifier,
		description nvarchar(max),
		sort_order int
	)
as
	begin
	insert into @result
		select	id, name, parent_id, description, sort_order
		from	dbo.topics t
				inner join dbo.callTopics ct on ct.topicId = t.id
		where	ct.callId = @callId
		order by sort_order desc, name asc
	return
	end
go

/* END UPDATE SCRIPT */
go
if @@error<>0 and @@trancount>0 rollback transaction
go

if @@trancount=0 begin
	update #tmpProperties set value = 1 where name = 'error'
	begin transaction
end
go

if (select top 1 value from #tmpProperties where name = 'error') <> '0' rollback transaction
go

if @@trancount > 0 begin
	declare @@scriptName nvarchar(255)
	select top 1 @@scriptName = value from #tmpProperties where name = 'script'

	if dbo.scriptExisted(@@scriptName) = 1 begin
		rollback transaction
		print @@scriptName + ' already executes'
	end
	else begin
		exec dbo.updateScript @@scriptName
		commit transaction
		print 'The database update succeeded'
	end
END
else begin
	print 'The database update failed'
end
go

drop table #tmpProperties
go
